/*
 * PhysicalDAO.java
 *
 * Created on March 5, 2007, 8:11 PM
 *
 */

package edu.byu.isys413.group1E.data;

import edu.byu.isys413.group1E.handlers.*;
import java.sql.*;
import java.util.ArrayList;

/** This is a public abstract class through which the multiple types of Physical will be accessed.
 *
 * @author Kendall Frazier
 */
public abstract class PhysicalDAO extends ProductDAO{
    
    /** Contains a singleton instance of PhysicalDAO*/
    private static PhysicalDAO instance = null;

    /**Retrieves an instance of the PhysicalDAO object.
     * This method is overridden in all subclasses, but is defined here to enable
     * the other classes getInstance methods to be accessed.
     *
     * @return instance
     */
    public static synchronized PhysicalDAO getInstance(){
        return instance;
    }
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   CREATE
    /**
     * Abstract method to require every extending class to have a create method.
     * 
     * @return a newly created PhysicalBO with unique GUID
     * @throws java.lang.Exception to catch any errors in the creation process.
     */
    public abstract PhysicalBO create() throws Exception;

    //////////////////////////////////////////////////////////////////////////////
    //////////   READ
    /**
     * This is the public abstract read statement.  It requires all extending classes to have a 
     * read method to load an existing PhysicalBO record
     * from the database based on the RevSrcID and return that object.
     * 
     * @return a PhysicalBO containing the information extracted from the requested
     * table and tuple in the DB.
     * @param id contains the unique identifier for the record to be read from the DB.
     * @throws edu.byu.isys413.group1E.DataException to catch any errors with the incoming data
     * @throws edu.byu.isys413.group1E.ConnectionPoolException to catch errors when connecting to the DB
     */
    public abstract ProductBO read(String id) throws SQLException, DataException, ConnectionPoolException;
    
    
    /**
     * This is a public abstract read statement that is called after obtaining
     * a connection with the DB.  It loads an existing Physical record
     * from the database based on the RevSrcID given, returning the requested BO.
     *
     * The subclasses to this class call this method, which reads the object from the cache, 
     * reads the data from the Revenue_Source table that is universal across revenue sources, then 
     * puts the object back in the cache after updating it with the info.
     * 
     * @return PhysicalBO
     * @param id contains the given id for the Employee to be read
     * @param conn contains an already open connection to the DB
     * @throws edu.byu.isys413.group1E.ConnectionPoolException catches any errors with the connection
     * @throws edu.byu.isys413.group1E.DataException catches any errors with incoming data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     */   
    public synchronized ProductBO read(String id, Connection conn) throws SQLException, DataException, ConnectionPoolException{
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        PhysicalBO physBO = (PhysicalBO)c.get(id);
        
        ConceptualDAO conceptualDAO = ConceptualDAO.getInstance();
        StoreDAO storeDAO = StoreDAO.getInstance();
        StoreBO storeBO = null;
        ConceptualBO conceptualBO = null;
        
        //put it to the cache so the super.read can get it
        c.put(id, physBO);
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        physBO = (PhysicalBO)super.read(id, conn);
        
        // get a result set from
        // a SELECT * FROM table WHERE id=guid
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Physical_Product WHERE ProductID = '"+id+"'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            physBO.setProductID(rs.getString("ProductID"));
            
            conceptualBO = (ConceptualBO)conceptualDAO.read(rs.getString("cproductid"), conn);
            physBO.setConceptual(conceptualBO);
            
            storeBO = storeDAO.read(rs.getString("storeid"), conn);
            physBO.setStore(storeBO);
            
            physBO.setShelfLocation(rs.getString("shelflocation"));            
            physBO.setDatePurchased(rs.getDate("datePurchased").toString());
            physBO.setPhysType(rs.getString("phystype"));
            
            // save to the cache
            c.put(physBO.getProductID(), physBO);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return physBO;
    }
    
    /*Retrieve list of all revSrcomers*/
    /**
     * 
     * @throws edu.byu.isys413.group1E.DataException 
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.group1E.ConnectionPoolException 
     * @return 
     */
    public abstract ArrayList<ProductBO> readAll() throws DataException, SQLException, ConnectionPoolException;

    /**
     * 
     * @param xid 
     * @throws edu.byu.isys413.group1E.DataException 
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.group1E.ConnectionPoolException 
     * @return 
     */
    public abstract ProductBO readBySKU(String xid) throws DataException, SQLException, ConnectionPoolException;
    
    
    //////////////////////////////////
    ///   UPDATE
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     */
          
    
    public void insertPhysical(String id, Connection conn) throws SQLException, DataException, ConnectionPoolException{
        Cache c = Cache.getInstance();
        PhysicalBO physBO = (PhysicalBO)c.get(id);
//        super.save(physBO, conn);
        
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Physical_Product (productid, cproductid, storeid, shelflocation, datepurchased, phystype) VALUES (?, ?, ?, ?, ?, ?)");
        
        pstmt.setString(1, physBO.getProductID());
        pstmt.setString(2, physBO.getConceptual().getProductID());
        pstmt.setString(3, physBO.getStore().getStoreID());
        pstmt.setString(4, physBO.getShelfLocation());
        pstmt.setDate(5, java.sql.Date.valueOf(physBO.getDatePurchased()));
        pstmt.setString(6, physBO.getPhysType());
        
        pstmt.execute();
        pstmt.close();
        physBO.setAlreadyInDB(true);
        
        c.put(physBO.getProductID(), physBO);
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    public void updatePhysical(String id, Connection conn) throws SQLException, DataException, ConnectionPoolException{
        Cache c = Cache.getInstance();
        PhysicalBO physBO = (PhysicalBO)c.get(id);
//        super.save(physBO, conn);
        
        PreparedStatement pstmt = conn.prepareStatement("UPDATE Physical_product SET productID = ?, CProductID = ?, storeid = ?, shelflocation = ?, datepurchased = ?, phystype = ? WHERE ProductID = ?");
        
        pstmt.setString(1, physBO.getProductID());
        pstmt.setString(2, physBO.getConceptual().getProductID());
        pstmt.setString(3, physBO.getStore().getStoreID());
        pstmt.setString(4, physBO.getShelfLocation());
        pstmt.setDate(5, java.sql.Date.valueOf(physBO.getDatePurchased()));
        pstmt.setString(6, physBO.getPhysType());
        pstmt.setString(7, physBO.getProductID());
        
        pstmt.execute();
        pstmt.close();
        physBO.setAlreadyInDB(true);
        
        c.put(physBO.getProductID(), physBO);
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    abstract void insert(ProductBO bo, Connection conn) throws SQLException, DataException, ConnectionPoolException;
//    {
//        //update the DB
//        Cache c = Cache.getInstance();
//        PhysicalBO physBO = (PhysicalBO)c.get(id);
//        
//        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Physical_Product (ProductID, CProductID, storeid, shelflocation, datepurchased, phystype)" +
//                "VALUES (?, ?, ?, ?, ?, ?)");
//        pstmt.setString(1, physBO.getProductID());
//        pstmt.setString(2, physBO.getConceptual().getProductID());
//        pstmt.setString(3, physBO.getStore().getStoreID());
//        pstmt.setString(4, physBO.getShelfLocation());
//        pstmt.setDate(5, java.sql.Date.valueOf(physBO.getDatePurchased()));
//        pstmt.setString(6, physBO.getPhysType());
//        
//        pstmt.executeUpdate();
//        pstmt.close();
//        // tell the object that it's now in the db (so we call update next time not insert)
//        physBO.setAlreadyInDB(true);
//    }
    
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   DELETE
    
    //I did not include the DELETE functionality intentionally due to instructions received, as well as 
    //to protect the integrity of the data.

}
